#绘制折线图
library(tidyverse)
library(lubridate)

conn <- src_sqlite("~/Documents/rockontrol/合肥项目数据/合肥市项目.db")

begin <- "2019-08-05"
end <- "2019-08-11"

town <-  "包河区"

dataset <- tbl(conn,"日数据") %>%
  inner_join(tbl(conn,"站点信息"),by  =c("站点名称"="站点")) %>%
  filter(类型 == "国控站",日期>= begin,日期<= end,区县==town)  %>% collect()

avg <- tbl(conn,"日数据") %>%
  inner_join(tbl(conn,"站点信息"),by  =c("站点名称"="站点")) %>%
  filter(类型 == "国控站",日期>= begin,日期<= end)  %>% collect()%>% mutate(日期 = ymd(日期))%>%group_by(日期)%>%
  summarize(NO2浓度 = mean(NO2浓度,na.rm = TRUE),PM2.5浓度 = mean(PM2.5浓度,na.rm = TRUE),PM10浓度 = mean(PM10浓度,na.rm = TRUE)) %>%
  mutate (站点名称 = "均值") %>% select(站点名称,日期 = 日期,NO2浓度, PM2.5浓度, PM10浓度)

dataset  %>%mutate(日期 = ymd(日期))%>%
  union_all(avg)%>%
 # mutate("PM[10]/PM[2.5]" = PM10浓度/PM2.5浓度)%>%
  select (站点名称,日期,"NO[2]~ug/m^3" = NO2浓度,"PM[2.5]~ug/m^3" = PM2.5浓度,"PM[10]~ug/m^3" = PM10浓度) %>%
  gather(key = 项,value = 浓度,... = 3:5)%>%
  ggplot(aes(x = 日期,y = 浓度,group = 站点名称,color= 站点名称)) + 
  geom_line(size = 1)+
  scale_x_date(date_breaks = "1 day",date_labels = "%m月%d日")+
  facet_grid(项~.,scales = "free_y",labeller = label_parsed)+
  labs (x= "",y = "浓度",fill=NULL)+
  theme_bw() + theme(  plot.title =   element_text(hjust = 0.5),
                       axis.text.x=element_text(angle = -90,size = 15,vjust = 0.5),
                       axis.title.y = element_text(size = 15),
                       axis.text.y = element_text(size = 15),
                       strip.text = element_text(size= 15),legend.text.align = 0,legend.title = element_blank(),
                       legend.text = element_text(size = 15)
                       
                       ) 


DBI::dbDisconnect(conn$con)


